#!/usr/bin/env python3
import pymysql

# 连数据库创库创表
db = pymysql.connect(host='localhost', user='root', passwd='123456')
cur = db.cursor()
cur.execute('create database sanle_1')
cur.execute('use sanle_1')
cur.execute("""create table student(
id int primary key,
name varchar(20),
age int,
phone_num decimal(11),
address varchar(100),
major varchar(50)
)""")
INSERT_FORMAT = """insert into student(id,name,age,phone_num,address,major)
values({0},'{1}',{2},{3},'{4}','{5}')"""

cur.execute(
    INSERT_FORMAT.format(1, 'cali', 30, 17688888888, 'sanchuangtongle',
                         'python'))
cur.execute(
    INSERT_FORMAT.format(2, 'lica', 22, 17666666666, 'sanchuangtongle',
                         'linux'))
cur.execute(
    INSERT_FORMAT.format(3, 'ells', 21, 17600000000, 'sanchuangtongle',
                         'shell'))
db.commit()

# 查询
OUTPUT_FORMAT = '{0:>5}  {1:10} {2:3} {3:>15}  {4:30} {5:10}'
FIELDS = ('id', 'name', 'age', 'phone_num', 'address', 'major')
cur.execute('select id,name,age,phone_num,address,major from student')
datas = cur.fetchall()
print(OUTPUT_FORMAT.format(*FIELDS))
for i in datas:
    print(OUTPUT_FORMAT.format(*i))

# 关闭连接
db.close()
